﻿#include "businesssql.h"

#include <Debug/DEBUG.hpp>
#include <FileManager/filemanager.h>
#include <AppConfig/appconfig.h>

BusinessSQL::BusinessSQL() {}

void BusinessSQL::init() {
    AppConfig& config = AppConfig::getInstance();
    std::string host = config.globalValue("SQLHostName");
    std::string port = config.globalValue("SQLPort");
    std::string userName = config.globalValue("SQLUserName");
    std::string userPwd = config.globalValue("SQLUserPwd");
    std::string sql = "host=" + host + " port=" + port + " user=" + userName + " password=" + userPwd;
    DEBUG_INFO(sql);
    dbPool = std::make_shared<soci::connection_pool>(POOL_SIZE);
    // 初始化连接池
    for (size_t i = 0; i < POOL_SIZE; i++) {
        soci::session &db = dbPool->at(i);
        db.open(soci::mysql, sql);
    }
}

void BusinessSQL::useDatabase() {
    AppConfig& config = AppConfig::getInstance();
    std::string dbname = config.runtimeValue("Account");
    // 检查目标数据库是否存在
    bool flag = false;
    try {
        soci::session db(*dbPool);
        int dbCount = 0;
        db << "SELECT COUNT(SCHEMA_NAME) FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = :dbName", soci::use(dbname), soci::into(dbCount);
        if (dbCount == 0) {
            // 数据库不存在，创建数据库
            db << "CREATE DATABASE " + dbname + " CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci";
            flag = false;
        } else {
            flag = true;
        }
    } catch (const soci::soci_error& e) {
        std::cerr << "SOCI error: " << e.what() << std::endl;
    } catch (const std::exception& e) {
        std::cerr << "Error: " << e.what() << std::endl;
    }

    // 初始化连接池中的所有的连接
    for (size_t i = 0; i < POOL_SIZE; i ++) {
        soci::session &db = dbPool->at(i);
        std::string sql = "USE " + dbname;
        db << sql;
    }
    // 如果是新创建的数据库，则初始化数据库
    if (!flag) {
        createTable();
    }
}

void BusinessSQL::createTable()
{
    std::string sqls[2];
    sqls[0] = R"(CREATE TABLE user_messages (
                    message_id INT AUTO_INCREMENT PRIMARY KEY,
                    sender_id INT NOT NULL,
                    receiver_id INT NOT NULL,
                    message TEXT NOT NULL,
                    type INT NOT NULL,
                    sent_at TEXT NOT NULL
                );)";
    sqls[1] = R"(CREATE TABLE group_messages (
                    message_id INT AUTO_INCREMENT PRIMARY KEY,
                    group_id INT NOT NULL,
                    sender_id INT NOT NULL,
                    message TEXT NOT NULL,
                    type INT NOT NULL,
                    sent_at TEXT NOT NULL
                );)";

    soci::session db(*dbPool);
    for (int i = 0 ; i < 2; i ++) {
        try {
            db << sqls[i].c_str();
        } catch (const soci::soci_error& e) {
            std::cerr << "SOCI error: " << e.what() << std::endl;
        } catch (const std::exception& e) {
            std::cerr << "Error: " << e.what() << std::endl;
        }
    }
}

void BusinessSQL::saveUserMessage(uint64_t senderID, uint64_t receiverID, nlohmann::json message)
{
    uint64_t messageID = message["MessageID"];
    std::string messageStr = message["Message"];
    std::string messageTime = message["Time"];
    int messageType = message["MessageType"];

    std::string sql = "INSERT INTO user_messages (message_id, sender_id, receiver_id, message, sent_at, type) "
                      "VALUES (:messageID, :senderID, :receiverID, :messageStr, :messageTime, :messageType);";

    soci::session db(*dbPool);
    try {
        db << sql, soci::use(messageID),
            soci::use(senderID),
            soci::use(receiverID),
            soci::use(messageStr),
            soci::use(messageTime),
            soci::use(messageType);
    } catch (const soci::soci_error& e) {
        std::cerr << "SOCI error: " << e.what() << std::endl;
    } catch (const std::exception& e) {
        std::cerr << "Error: " << e.what() << std::endl;
    }
}

nlohmann::json BusinessSQL::queryLatest20UserMessage(uint64_t senderID, uint64_t receiverID)
{
    nlohmann::json result;
    soci::session db(*dbPool);
    std::string sql = "SELECT sender_id, message, sent_at, type FROM user_messages "
                      "WHERE (sender_id = :senderID AND receiver_id = :receiverID) OR (sender_id = :receiverID AND receiver_id = :senderID) "
                      "ORDER BY message_id DESC LIMIT 20";

    try {
        soci::rowset<soci::row> rs = (db.prepare << sql.c_str(), soci::use(senderID), soci::use(receiverID), soci::use(receiverID), soci::use(senderID));
        for (auto it = rs.begin(); it != rs.end(); it ++) {
            const soci::row& row = *it;
            nlohmann::json messageInfo;
            messageInfo["SenderID"] = row.get<int>("sender_id");
            messageInfo["Message"] = row.get<std::string>("message");
            messageInfo["Time"] = row.get<std::string>("sent_at");
            messageInfo["MessageType"] = row.get<int>("type");
            result.push_back(messageInfo);
        }
    } catch (const soci::soci_error& e) {
        std::cerr << "SOCI error: " << e.what() << std::endl;
    } catch (const std::exception& e) {
        std::cerr << "Error: " << e.what() << std::endl;
    }
    std::reverse(result.begin(), result.end());
    return result;
}

void BusinessSQL::saveGroupMessage(uint64_t senderID, uint64_t groupID, nlohmann::json message)
{
    soci::session db(*dbPool);
    uint64_t messageID = message["MessageID"];
    std::string messageStr = message["Message"];
    std::string messageTime = message["Time"];
    int messageType = message["MessageType"];

    std::string sql = "INSERT INTO group_messages (message_id, group_id, sender_id, message, sent_at, type) "
                      "VALUES (:messageID, :groupID, :senderID, :messageStr, :messageTime, :messageType);";
    try {
        db << sql.c_str(), soci::use(messageID),
            soci::use(groupID),
            soci::use(senderID),
            soci::use(messageStr),
            soci::use(messageTime),
            soci::use(messageType);
    } catch (const soci::soci_error& e) {
        std::cerr << "SOCI error: " << e.what() << std::endl;
    } catch (const std::exception& e) {
        std::cerr << "Error: " << e.what() << std::endl;
    }
}

nlohmann::json BusinessSQL::queryLatest30GroupMessage(uint64_t groupID)
{
    soci::session db(*dbPool);
    nlohmann::json result;
    std::string sql = "SELECT sender_id, message, sent_at, type "
                      "FROM group_messages "
                      "WHERE group_id = :groupID "
                      "ORDER BY message_id DESC "
                      "LIMIT 30;";

    try {
        soci::rowset<soci::row> rs = (db.prepare << sql.c_str(), soci::use(groupID));
        for (auto it = rs.begin(); it != rs.end(); it ++) {
            const soci::row& row = *it;
            nlohmann::json messageInfo;
            messageInfo["SenderID"] = row.get<int>("sender_id");
            messageInfo["Message"] = row.get<std::string>("message");
            messageInfo["Time"] = row.get<std::string>("sent_at");
            messageInfo["MessageType"] = row.get<int>("type");
            result.push_back(messageInfo);
        }
    } catch (const soci::soci_error& e) {
        std::cerr << "SOCI error: " << e.what() << std::endl;
    } catch (const std::exception& e) {
        std::cerr << "Error: " << e.what() << std::endl;
    }
    std::reverse(result.begin(), result.end());
    return result;
}
